const express = require('express');
const router = express.Router();

const sql = require('../db/index');

//分页获取产品列表
router.get('/category', async (req, res) => {
  //要分页，第一个查询那一页的数据，这一页数据有多少条 page = 1； pagesize=10
  let { page, pageSize } = req.query;
  let start = (page - 1) * pageSize;
  let [product_category] = await sql.query(`SELECT * FROM jz_product_category limit ${start},${pageSize}`);
  let [total] = await sql.query('SELECT COUNT(*) as total FROM jz_product_category');
  res.send({
    code: 200,
    message: '获取成功',
    data: product_category,
    total:total[0].total
  })
})



//新增或修改
router.post('/category/saveOrUpdate', async function (req, res) {
  let { id, name, icon, num } = req.body;

  let mysql = '';
  // 判断id是否存在 存在修改 不存在新增
  if(id){
    
    mysql = `update jz_product_category set name='${name}',
    icon='${icon}',num='${num}' where id = ${id}`
  } else{ 
   
    mysql = `insert into jz_product_category (id,name,icon,num,parent_id) values (null,'${name}','${icon}','${num}',null)`
  }
  sql.query(mysql)
  res.send({
    status:200,
    message:'操作成功',
    data:null,
    timestamp: new Date().getTime()


   })
  
});



//分页模糊查询
router.get('/category/pageQuery', async function (req, res) {
  let { page, pageSize, id, name, num } = req.query;
  console.log(req.query);
  page = +page;
  pageSize = +pageSize;
  id = +id;
  let result, result1;
  let offset = (page - 1) * pageSize;
  if (name && id && num) {
    let mysql = `select * from jz_product_category where name like '${'%' + name + '%'}' and id = '${id}' and num = ${num} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product_category where name like '${'%' + name + '%'}' and id = '${id}' and num = ${num} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name && id) {
    let mysql = `select * from jz_product_category where name like '${'%' + name + '%'}' and id = '${id}' limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product_category where name like '${'%' + name + '%'}' and id = '${id}' limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name && num) {
    let mysql = `select * from jz_product_category where name like '${'%' + name + '%'}' and num = ${num} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product_category where name like '${'%' + name + '%'}' and num = ${num} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (num && id) {
    let mysql = `select * from jz_product_category where num = '${num}' and id = ${id} limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product_category where num = '${num}'  and id = ${id} limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  } else if (name || num || id) {
    if (name) {
      let mysql = `select * from jz_product_category where name like '${'%' + name + '%'}' limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product_category where name like '${'%' + name + '%'}' limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    } else if (num) {
      let mysql = `select * from jz_product_category where num ='${num}' limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product_category where num = '${num}' limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    } else {
      let mysql = `select * from jz_product_category where id = ${id} limit ${pageSize} offset ${offset}`;
      result = await sql.query(mysql);
      let mysql1 = `select count(*) total from jz_product_category where id = ${id} limit ${pageSize} offset ${offset}`;
      result1 = await sql.query(mysql1);
    }
  } else {
    let mysql = `select * from jz_product_category  limit ${pageSize} offset ${offset}`;
    result = await sql.query(mysql);
    let mysql1 = `select count(*) total from jz_product_category  limit ${pageSize} offset ${offset}`;
    result1 = await sql.query(mysql1);
  }
  result = result.filter(item => {
    return item.length > 0;
  })
  res.send({
    status: 200,
    message: '查询成功',
    data: result[0],
    timestamp: new Date().getTime()
  })
})










module.exports = router;